/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2014/7/28 18:54:47                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CustomerOrder') and o.name = 'FK_CUSTOMER_REFERENCE_CUSTOMER')
alter table CustomerOrder
   drop constraint FK_CUSTOMER_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EntrySheet') and o.name = 'FK_ENTRYSHE_REFERENCE_CUSTOMER')
alter table EntrySheet
   drop constraint FK_ENTRYSHE_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('InterflowRecords') and o.name = 'FK_INTERFLO_REFERENCE_ORDERDET')
alter table InterflowRecords
   drop constraint FK_INTERFLO_REFERENCE_ORDERDET
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderDetail') and o.name = 'FK_ORDERDET_REFERENCE_CUSTOMER')
alter table OrderDetail
   drop constraint FK_ORDERDET_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PaymentRecord') and o.name = 'FK_PAYMENTR_REFERENCE_CUSTOMER')
alter table PaymentRecord
   drop constraint FK_PAYMENTR_REFERENCE_CUSTOMER
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Customer')
            and   type = 'U')
   drop table Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CustomerOrder')
            and   type = 'U')
   drop table CustomerOrder
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EntrySheet')
            and   type = 'U')
   drop table EntrySheet
go

if exists (select 1
            from  sysobjects
           where  id = object_id('InterflowRecords')
            and   type = 'U')
   drop table InterflowRecords
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OrderDetail')
            and   type = 'U')
   drop table OrderDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PaymentRecord')
            and   type = 'U')
   drop table PaymentRecord
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   CustomerID           uniqueidentifier     not null,
   Name                 nvarchar(50)         null,
   Address              nvarchar(50)         null,
   Mark                 nvarchar(100)        null,
   OperatorID           uniqueidentifier     null,
   CreateTime           datetime             null,
   constraint PK_CUSTOMER primary key (CustomerID)
)
go

/*==============================================================*/
/* Table: CustomerOrder                                         */
/*==============================================================*/
create table CustomerOrder (
   CustomerOrderID      uniqueidentifier     not null,
   CustomerID           uniqueidentifier     null,
   Code                 nvarchar(50)         null,
   BuzType              int                  null,
   CreateTime           datetime             null,
   OperatorID           uniqueidentifier     null,
   ContractName         nvarchar(50)         null,
   ContractPhone        nvarchar(50)         null,
   Mark                 nvarchar(500)        null,
   Status               int                  null,
   OrderType            int                  null,
   constraint PK_CUSTOMERORDER primary key (CustomerOrderID)
)
go

/*==============================================================*/
/* Table: EntrySheet                                            */
/*==============================================================*/
create table EntrySheet (
   EntrySheetID         uniqueidentifier     not null,
   CustomerOrderID      uniqueidentifier     null,
   OperatorName         nvarchar(50)         null,
   EntrySheetCode       nvarchar(50)         null,
   IsTax                bit                  null,
   IsDrawback           bit                  null,
   Duty                 decimal(18,2)        null,
   Expenses             decimal(18,2)        null,
   VAT                  decimal(18,2)        null,
   GoDate               datetime             null,
   IsChange             bit                  null,
   IsDelete             bit                  null,
   Reason               nvarchar(500)        null,
   IsConfirm            nvarchar(100)        null,
   RegFee               decimal(18,2)        null,
   ChangeFee            decimal(18,2)        null,
   CreateTime           datetime             null,
   OperatorID           uniqueidentifier     null,
   constraint PK_ENTRYSHEET primary key (EntrySheetID)
)
go

/*==============================================================*/
/* Table: InterflowRecords                                      */
/*==============================================================*/
create table InterflowRecords (
   InterflowRecordsID   uniqueidentifier     not null,
   OrderDetailID        uniqueidentifier     null,
   Common               nvarchar(50)         null,
   Danger               nvarchar(50)         null,
   Cold                 nvarchar(50)         null,
   StoreDays            float                null,
   TakeFee              decimal(18,2)        null,
   StoreFee             decimal(18,2)        null,
   StandFee             decimal(18,2)        null,
   OperatorID           uniqueidentifier     null,
   Driver               nvarchar(50)         null,
   CarNum               nvarchar(50)         null,
   DriverPhone          nvarchar(50)         null,
   constraint PK_INTERFLOWRECORDS primary key (InterflowRecordsID)
)
go

/*==============================================================*/
/* Table: OrderDetail                                           */
/*==============================================================*/
create table OrderDetail (
   OrderDetailID        uniqueidentifier     not null,
   CustomerOrderID      uniqueidentifier     null,
   TakeDate             datetime             null,
   DriverCellphone      nvarchar(50)         null,
   CarNum               nvarchar(50)         null,
   InStoreDate          datetime             null,
   Count                float                null,
   Weight               float                null,
   CalcWeight           float                null,
   DirectFlight         nvarchar(100)        null,
   Transmit             nvarchar(100)        null,
   ArrivedStore         nvarchar(100)        null,
   CreateTime           datetime             null,
   ArraviedDate         datetime             null,
   Code                 nvarchar(50)         null,
   constraint PK_ORDERDETAIL primary key (OrderDetailID)
)
go

/*==============================================================*/
/* Table: PaymentRecord                                         */
/*==============================================================*/
create table PaymentRecord (
   PaymentRecordID      uniqueidentifier     not null,
   CustomerOrderID      uniqueidentifier     null,
   Type                 int                  null,
   RecUnit              nvarchar(100)        null,
   PayUnit              nvarchar(100)        null,
   RecCode              nvarchar(100)        null,
   TaxCode              nvarchar(100)        null,
   Changer              nvarchar(100)        null,
   Amount               decimal(18,2)        null,
   CreateTime           datetime             null,
   OperatorID           uniqueidentifier     null,
   constraint PK_PAYMENTRECORD primary key (PaymentRecordID)
)
go

alter table CustomerOrder
   add constraint FK_CUSTOMER_REFERENCE_CUSTOMER foreign key (CustomerID)
      references Customer (CustomerID)
go

alter table EntrySheet
   add constraint FK_ENTRYSHE_REFERENCE_CUSTOMER foreign key (CustomerOrderID)
      references CustomerOrder (CustomerOrderID)
go

alter table InterflowRecords
   add constraint FK_INTERFLO_REFERENCE_ORDERDET foreign key (OrderDetailID)
      references OrderDetail (OrderDetailID)
go

alter table OrderDetail
   add constraint FK_ORDERDET_REFERENCE_CUSTOMER foreign key (CustomerOrderID)
      references CustomerOrder (CustomerOrderID)
go

alter table PaymentRecord
   add constraint FK_PAYMENTR_REFERENCE_CUSTOMER foreign key (CustomerOrderID)
      references CustomerOrder (CustomerOrderID)
go

